package com.maven.qqspider.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelEngine {

	/**
	 * load excel file.
	 * 
	 * @param excelFile
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static HSSFWorkbook loadTemplate(String excelFile)
			throws FileNotFoundException, IOException {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		return wb;
	}

	/**
	 * write the workbook into a file
	 * 
	 * @param destFileName
	 * @param wb
	 * @throws IOException
	 */
	@SuppressWarnings("unchecked")
	public static void writeAExcel(String fullName, List<String> colNameList,
			List<String> colTypeList, List<List<String>> dataList, Object... headers)
			throws IOException {
		FileOutputStream fileOut = new FileOutputStream(fullName);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		int rowNo = 0;
		if(headers.length>0){
			//create mergetd header
			HSSFCellStyle titleCellStyle = wb.createCellStyle();
			titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			
			rowNo = 1;
			HSSFRow row = sheet.createRow(0);
			List<ExcelHeader> headerList = (List<ExcelHeader>)headers[0];
			int col = 0;
			for(ExcelHeader header:headerList){
				HSSFCell cell = row.createCell(col);
				cell.setCellStyle(titleCellStyle);
				cell.setCellValue(new HSSFRichTextString(header.getHeader()));
				CellRangeAddress address = new CellRangeAddress(0,0,col,col+header.getColSpan()-1);
				sheet.addMergedRegion(address);
				col+=header.getColSpan();
			}
		}
		HSSFRow row = sheet.createRow(rowNo);
		for(int i=0;i<colNameList.size();i++){
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(new HSSFRichTextString(colNameList.get(i)));
		}
		for(int i=0;i<dataList.size();i++){
			row = sheet.createRow(i+1+rowNo);
			List<String> data = dataList.get(i);
			for(int j=0;j<data.size();j++){
				HSSFCell cell = row.createCell(j);
				String type = colTypeList.get(j);
				if(type.equals("String")){
					if(data.get(j)!=null && !data.get(j).equals("null"))
						cell.setCellValue(new HSSFRichTextString(data.get(j)));
					else
						cell.setCellValue(new HSSFRichTextString(""));
				}
				else if(type.equals("Double")){
					if(data.get(j)!= null && !data.get(j).trim().equals("") && !data.get(j).equals("null"))
						cell.setCellValue(Double.parseDouble(data.get(j)));
					else
						cell.setCellValue(new HSSFRichTextString(""));
				}
			}
		}
		wb.write(fileOut);
		fileOut.close();
	}

	/**
	 * read a value as a string, the value at line x, column y of the sheet
	 * @param sheet
	 * @param x
	 * @param y
	 * @return
	 */
	public static String readAsString(HSSFSheet sheet, int x, int y) {
		HSSFRow row = sheet.getRow((short) x);
		if(row == null)
			return null;
		HSSFCell cell = row.getCell(y);
		if (cell == null)
			return null;
		return readCellAsString(cell);
	}

	public static Date readDate(HSSFSheet sheet, int x, int y) {
		HSSFRow row = sheet.getRow((short) x);
		if(row == null)
			return null;
		HSSFCell cell = row.getCell(y);
		if (cell == null)
			return null;
		return cell.getDateCellValue();
	}

	public static Double readDouble(HSSFSheet sheet, int x, int y) {
		HSSFRow row = sheet.getRow((short) x);
		if(row == null)
			return null;
		HSSFCell cell = row.getCell(y);
		if (cell == null)
			return null;
		return cell.getNumericCellValue();
	}

	/**
	 * read a value as a string of a cell
	 * @param sheet
	 * @param x
	 * @param y
	 * @return
	 */
	public static String readCellAsString(HSSFCell cell) {  
        String cellValue = "";
        if(cell == null)
        	return "";
        switch  (cell.getCellType()) {  
        case  HSSFCell.CELL_TYPE_STRING:  
            cellValue = cell.getRichStringCellValue().getString();  
            if (cellValue.trim().equals( "" )||cellValue.trim().length()<= 0 )  
                cellValue=" " ;  
            break ;  
        case  HSSFCell.CELL_TYPE_NUMERIC:
            cellValue = ""+new Double(cell.getNumericCellValue()).intValue();
            break ;  
        case  HSSFCell.CELL_TYPE_FORMULA:  
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);  
            cellValue = String.valueOf(cell.getNumericCellValue());  
            break ;  
        case  HSSFCell.CELL_TYPE_BLANK:  
            cellValue=" " ;  
            break ;  
        case  HSSFCell.CELL_TYPE_BOOLEAN:  
            break ;  
        case  HSSFCell.CELL_TYPE_ERROR:  
            break ;  
        default :  
            break ;  
        }  
        return  cellValue;  
    } 

}
